package com.interview.write;

public class DemoSql {
    //查询物料主表只含有单号3012的数据
    //只有表二（y_marc） 有单号字段
    /**
     *   SELECT  b.* FROM (SELECT * FROM y_matmain) AS b LEFT JOIN (SELECT MATNR,werks FROM y_marc) AS a
     *   ON a.MATNR = b.MATNR WHERE werks = '3012'
     *     GROUP BY b.MATNR HAVING COUNT(*) > 1
     *
     *     SELECT DISTINCT b.* FROM (SELECT * FROM y_matmain) AS b LEFT JOIN (SELECT MATNR,werks FROM y_marc) AS a
     *     ON a.MATNR = b.MATNR WHERE werks = '3012'
     */


    //查询每门课程都大于80分以上的学生
    //只有表一（Student_Name） 有姓名字段
    /**
     * SELECT Student_Name FROM Students WHERE Student_ID IN (SELECT Student_ID FROM Student_Scores GROUP BY Student_ID HAVING MIN(Score)> 80);
     */

    //更新表Student_Scores中的Pass_Or_Fail列，值为“Pass”(如果成绩为>= 60)或值为“Fail”(如果成绩< 60)，且该生的专业必须为“软件工程”。
    /**
     * UPDATE Student_Scores SET Pass_Or_Fail=CASE WHEN Score >= 60 THEN 'Pass'
     * WHEN Score < 60 THEN 'Fail'
     * END WHERE  Course_Name = '软件工程';
     *
     * UPDATE studenttb SET sex=CASE WHEN salary > 80 THEN '及'
     *  WHEN salary <= '80' THEN '不'
     * ELSE 'Q'
     * END WHERE state = '5';
     */

    //查询001比002课程成绩高 的所有学生学号 一张表
    //S 学号 SC分数表
    /**
     *SELECT a.S FROM (SELECT S,score FROM SC WHERE C = 001)a ,(SELECT S,score FROM SC WHERE C= 002)b WHERE a.score>b.score AND a.S=b.S
     */

    //查询所有同学的学号(S#)、姓名(Sname)、选课数、总成绩   C# 课程编号
    /**
     *select Student.S#,Student.Sname,count(SC.C#),sum(score) from Student left Outer join SC on Student.S#=SC.S#
     * group by Student.S#,Sname
     */
}
